# -*- coding: utf-8 -*-
# !/usr/bin/env python
import pymysql
import pandas as pd


class DB:
    def __init__(self, engine):
        self._engine = engine
        self._conn = pymysql.connect(host=self._engine['host'],
                                     user=self._engine['user'],
                                     password=self._engine['password'],
                                     port=self._engine['port'],
                                     database=self._engine['database'],
                                     charset='utf8')

    def append(self, dataframe, table):
        """
        向数据库追加数据
        :param dataframe: 一个dataframe类型数据
        :param table: 表名
        :return: 插入数据条数
        """
        cursor = self._conn.cursor()
        col = ','.join(dataframe.columns)
        value = ','.join(['%s'] * dataframe.shape[1])
        sql = """
        INSERT INTO {0} ({1}) 
        VALUES ({2});
        """.format(table, col, value)
        cursor.executemany(sql, [tuple(i) for i in dataframe.values])
        try:
            self._conn.commit()
            length = dataframe.shape[0]
        except Exception as err:
            self._conn.rollback()
            length = 0
            raise Exception("数据写入失败！！！")
        return length

    def replace(self, dataframe, table):
        """
        向数据库追加数据
        :param dataframe: 一个dataframe类型数据
        :param table: 表名
        :return: 插入数据条数
        """
        self.delete(table)
        cursor = self._conn.cursor()
        col = ','.join(dataframe.columns)
        value = ','.join(['%s'] * dataframe.shape[1])
        sql = """
        INSERT INTO {0} ({1}) 
        VALUES ({2});
        """.format(table, col, value)
        cursor.executemany(sql, [tuple(i) for i in dataframe.values])
        try:
            self._conn.commit()
            length = dataframe.shape[0]
        except Exception as err:
            self._conn.rollback()
            length = 0
            raise Exception("数据写入失败！！！")
        return length

    def insert(self, dataframe, table, key=[]):
        """插入dataframe类型数据到指定表"""
        with self._conn.cursor() as cursor:
            col = ','.join(dataframe.columns)
            par = ','.join(['%s'] * dataframe.shape[1])
            keys = ','.join([f'{i} = values({i})' for i in dataframe.columns if i not in key])
            sql = f'insert into {table} ({col}) values({par}) on duplicate key update {keys};'
            cursor.executemany(sql, [tuple(i) for i in dataframe.values])
            try:
                self._conn.commit()
                msg = f"""数据写入成功：共{dataframe.shape[0]}条数据写入{table}表！！！"""
            except Exception as err:
                self._conn.rollback()
                msg = f"数据写入失败！！！"
                raise Exception(msg)
        return msg

    def select(self, sql, kind=True):
        """查询数据：kind为True表示需要输出数据"""
        with self._conn.cursor() as cursor:
            try:
                cursor.execute(sql)
                if kind:
                    result = cursor.fetchall()
                    cols = cursor.description
                    return pd.DataFrame(result, columns=[i[0] for i in cols])
                else:
                    return self._conn.commit()
            except Exception as err:
                self._conn.rollback()
                msg = f"ERROR - {self._engine['host']} session init failed: {err}" + "\n"
                raise Exception(msg)

    def tables(self):
        """查看表"""
        sql = "show tables;"
        return self.select(sql)

    def drop(self, table):
        """删除表"""
        sql = f"drop table {table};"
        return self.select(sql, kind=False)

    def delete(self, table, tag='1=1'):
        """删除数据，可以按条件删除"""
        sql = f"delete from {table} where {tag};"
        return self.select(sql, kind=False)

    def desc(self, table):
        """查看表结构"""
        sql = f"desc {table};"
        return self.select(sql)

    def process(self):
        """查看数据库进程"""
        sql = r"show processlist;"
        return self.select(sql)

    def kill(self, process_id):
        """关闭数据库进程"""
        sql = f"kill {process_id};"
        return self.select(sql, kind=False)

    def use(self, database):
        """切换数据库"""
        sql = f"use {database};"
        return self.select(sql, kind=False)

    def close(self):
        self._conn.close()

    def create_sql(self, table):
        sql = f"""show create table {table}"""
        dataframe = self.select(sql)
        return print(dataframe['Create Table'][0])
